using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SqlClient;
using PropertyManagement.Core.Model;

namespace PropertyManagement.Core.DataAccess {
        public static partial class SystemUserDataAccess {
             
            public static SystemUser GetById(int id){
                var sql = @"select * from dbo.SystemUser where SystemUserID = @id ";
                var paramList = new[] {
                    new SqlParameter("id", id)
                };
                using (var iReader = SqlServerHelper.ExecuteReader(SqlServerHelper.ConnectionString, CommandType.Text, sql,
                                                                paramList)) {
                    if (iReader.Read( )) {
                            return CreationSystemUserByIReader(iReader);
                    };
                    return null;
                }
            }

            public static void Add(SystemUser entity){
                  var sql = @"insert into dbo.SystemUser values (@username,@password,@rightlevel,@passwordquestion1,@passwordquestion2,@passwordquestion3,@passwordanswer1,@passwordanswer2,@passwordanswer3,@isvalid,@creationdate,@lastupdate,@creationuserid,@lastactionuserid) ;   select SCOPE_IDENTITY (); ";
                  var paramList = new SqlParameter[] {
							new SqlParameter("creationdate",entity.CreationDate),
							new SqlParameter("creationuserid",entity.CreationUserID),
							new SqlParameter("isvalid",entity.IsValid),
							new SqlParameter("lastactionuserid",entity.LastActionUserID),
							new SqlParameter("lastupdate",entity.LastUpdate),
							new SqlParameter("password",entity.Password),
							new SqlParameter("passwordanswer1",entity.PasswordAnswer1),
							new SqlParameter("passwordanswer2",entity.PasswordAnswer2),
							new SqlParameter("passwordanswer3",entity.PasswordAnswer3),
							new SqlParameter("passwordquestion1",entity.PasswordQuestion1),
							new SqlParameter("passwordquestion2",entity.PasswordQuestion2),
							new SqlParameter("passwordquestion3",entity.PasswordQuestion3),
							new SqlParameter("rightlevel",entity.RightLevel),
							new SqlParameter("username",entity.UserName)
                        };
                  using ( var ireader = SqlServerHelper.ExecuteReader(SqlServerHelper.ConnectionString, CommandType.Text, sql,
                                                            paramList)) {
                        if (ireader.Read()) {
                            entity.SystemUserID = int.Parse(ireader[0].ToString());
                        }
                  }
            
            }   

            public static void Remove(SystemUser entity) {
                var sql = @"delete from dbo.SystemUser where SystemUserID = @id";
                var paramList = new[] {
                    new SqlParameter("id", entity.SystemUserID)
                };
                SqlServerHelper.ExecuteNonQuery(SqlServerHelper.ConnectionString, CommandType.Text, sql, paramList);
            }   

            public static IEnumerable<SystemUser> LoadAll(){
                    var sql = " select * from dbo.SystemUser ";
                    var list = new List<SystemUser>( );
                    using (var iReader = SqlServerHelper.ExecuteReader(SqlServerHelper.ConnectionString, CommandType.Text, sql)) {
                        while (iReader.Read( )) {
                                list.Add(CreationSystemUserByIReader(iReader));
                        };
                    }  
                    return list;
            }   
        
            public static void Update(SystemUser entity){
                var sql = @" update dbo.SystemUser set CreationDate=@creationdate,CreationUserID=@creationuserid,IsValid=@isvalid,LastActionUserID=@lastactionuserid,LastUpdate=@lastupdate,Password=@password,PasswordAnswer1=@passwordanswer1,PasswordAnswer2=@passwordanswer2,PasswordAnswer3=@passwordanswer3,PasswordQuestion1=@passwordquestion1,PasswordQuestion2=@passwordquestion2,PasswordQuestion3=@passwordquestion3,RightLevel=@rightlevel,UserName=@username where SystemUserID = @id ";
                var paramList = new SqlParameter[] {
                            new SqlParameter("id",entity.SystemUserID),
							new SqlParameter("creationdate",entity.CreationDate),
							new SqlParameter("creationuserid",entity.CreationUserID),
							new SqlParameter("isvalid",entity.IsValid),
							new SqlParameter("lastactionuserid",entity.LastActionUserID),
							new SqlParameter("lastupdate",entity.LastUpdate),
							new SqlParameter("password",entity.Password),
							new SqlParameter("passwordanswer1",entity.PasswordAnswer1),
							new SqlParameter("passwordanswer2",entity.PasswordAnswer2),
							new SqlParameter("passwordanswer3",entity.PasswordAnswer3),
							new SqlParameter("passwordquestion1",entity.PasswordQuestion1),
							new SqlParameter("passwordquestion2",entity.PasswordQuestion2),
							new SqlParameter("passwordquestion3",entity.PasswordQuestion3),
							new SqlParameter("rightlevel",entity.RightLevel),
							new SqlParameter("username",entity.UserName)
                        };
                SqlServerHelper.ExecuteNonQuery(SqlServerHelper.ConnectionString, CommandType.Text, sql, paramList);
            }
            
            public static bool Exists(SystemUser entity){
                return entity.SystemUserID != 0 && GetById(entity.SystemUserID)!=null;
            }


            private static SystemUser CreationSystemUserByIReader(IDataReader iReader){
                if(iReader!=null){
                      return new SystemUser{
							CreationDate = iReader["CreationDate"] is DBNull ? default(DateTime) : (DateTime)iReader["CreationDate"] ,
							CreationUserID = iReader["CreationUserID"] is DBNull ? default(Int32) : (Int32)iReader["CreationUserID"] ,
							IsValid = iReader["IsValid"] is DBNull ? default(Boolean) : (Boolean)iReader["IsValid"] ,
							LastActionUserID = iReader["LastActionUserID"] is DBNull ? default(Int32) : (Int32)iReader["LastActionUserID"] ,
							LastUpdate = iReader["LastUpdate"] is DBNull ? default(DateTime) : (DateTime)iReader["LastUpdate"] ,
							Password = iReader["Password"] is DBNull ? default(String) : (String)iReader["Password"] ,
							PasswordAnswer1 = iReader["PasswordAnswer1"] is DBNull ? default(String) : (String)iReader["PasswordAnswer1"] ,
							PasswordAnswer2 = iReader["PasswordAnswer2"] is DBNull ? default(String) : (String)iReader["PasswordAnswer2"] ,
							PasswordAnswer3 = iReader["PasswordAnswer3"] is DBNull ? default(String) : (String)iReader["PasswordAnswer3"] ,
							PasswordQuestion1 = iReader["PasswordQuestion1"] is DBNull ? default(String) : (String)iReader["PasswordQuestion1"] ,
							PasswordQuestion2 = iReader["PasswordQuestion2"] is DBNull ? default(String) : (String)iReader["PasswordQuestion2"] ,
							PasswordQuestion3 = iReader["PasswordQuestion3"] is DBNull ? default(String) : (String)iReader["PasswordQuestion3"] ,
							RightLevel = iReader["RightLevel"] is DBNull ? default(RightLevel) : (RightLevel)iReader["RightLevel"] ,
							SystemUserID = iReader["SystemUserID"] is DBNull ? default(Int32) : (Int32)iReader["SystemUserID"] ,
							UserName = iReader["UserName"] is DBNull ? default(String) : (String)iReader["UserName"] 
                        };
                }
                return null; 
            }

            
            
            public static SystemUser GetByPKs(SystemUser entity){
                var sql =  @"select *  from dbo.SystemUser where UserName = @username ";
                var paramList = new SqlParameter[] {
							new SqlParameter("username",entity.UserName)
                        };
                 using (var iReader = SqlServerHelper.ExecuteReader(SqlServerHelper.ConnectionString, CommandType.Text, sql,
                                                                paramList)) {
                    if (iReader.Read( )) {
                            return CreationSystemUserByIReader(iReader);
                    };
                    return null;
                }
            }

            public static bool ExistsByPKs(SystemUser entity){
                var sql =  @"select *  from dbo.SystemUser where UserName = @username ";
                var paramList = new SqlParameter[] {
							new SqlParameter("username",entity.UserName)
                        };
                 using (var iReader = SqlServerHelper.ExecuteReader(SqlServerHelper.ConnectionString, CommandType.Text, sql,
                                                                paramList)) {
                    return iReader.Read( );
                }
            }

    }
}